citus
citus copied to clipboard
INCORRECT result from a distributed table full outer join a reference table
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!