Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

A logic bug caused by a UPDATE statement with distributed table #7782

Open
duerwuyi opened this issue Dec 9, 2024 · 0 comments
Open

A logic bug caused by a UPDATE statement with distributed table #7782

duerwuyi opened this issue Dec 9, 2024 · 0 comments

Comments

@duerwuyi
Copy link

duerwuyi commented Dec 9, 2024

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 
);

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

DROP TABLE IF EXISTS t7;
create table t7 ( 
vkey int4 ,
pkey int4
);

SELECT create_distributed_table('t3', 'c9');

insert into t0 (vkey, pkey, c0) values 
(3, 13000, make_timestamp(2032, 9, 4, 13, 38, 0));

query:

update t0 set 
  vkey = 117
where (((t0.pkey) in (select t7.vkey from t7 where false
        union all
        select t3.pkey from t3 where false
        ))) 
  or TRUE;

result: 0 rows updated

Comparison

without SELECT create_distributed_table('t3', 'c9'); (or without citus):

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

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

DROP TABLE IF EXISTS t7;
create table t7 ( 
vkey int4 ,
pkey int4
);

insert into t0 (vkey, pkey, c0) values 
(3, 13000, make_timestamp(2032, 9, 4, 13, 38, 0));

update t0 set 
  vkey = 117
where (((t0.pkey) in (select t7.vkey from t7 where false
        union all
        select t3.pkey from t3 where false
        ))) 
  or TRUE;

result: 1 row updated

OR we can use the same WHERE clause:

select t0.vkey 
from t0
where (((t0.pkey) in (select t7.vkey from t7 where false
        union all
        select t3.pkey from t3 where false
        ))) 
  or TRUE;

result: 1 row returned

EXPLAIN on the bug query

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

@duerwuyi duerwuyi changed the title A logical bug caused by a UPDATE statement A logic bug caused by a UPDATE statement Dec 9, 2024
@duerwuyi duerwuyi changed the title A logic bug caused by a UPDATE statement A logic bug caused by a UPDATE statement with distributed table Dec 9, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant