citus icon indicating copy to clipboard operation
citus copied to clipboard

INCORRECT result from a distributed table full outer join a reference table

Open duerwuyi opened this issue 1 year ago • 0 comments

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

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

insert into t2 (vkey) values (5);

SELECT create_distributed_table('t1', 'vkey');
SELECT create_reference_table('t2');

select * from (t2 full outer join t1 on(t2.vkey = t1.vkey ))
where not((85) in (select 1 from t2));

RESULT: 32 rows returned 5 | null 5 | null 5 | null ... (32 rows)

but, when I reproduce them without these two statements: SELECT create_distributed_table('t1', 'vkey'); and SELECT create_reference_table('t2'); , I got another RESULT, which is correct: 1 row returned vkey | vkey 5 | null

especially, the following query returns a correct result in distributed version.

select * from (t2 full outer join t1 on(t2.vkey = t1.vkey ))
where TRUE;

1 row returned vkey | vkey 5 | null

this bug happens in citus 12.3 and 12.5. Thanks a lot if someone could check it!

duerwuyi avatar Oct 04 '24 14:10 duerwuyi