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 of subquery with reference table in citus 13.0.1 #7891

Open
duerwuyi opened this issue Feb 6, 2025 · 0 comments
Open

A logic bug of subquery with reference table in citus 13.0.1 #7891

duerwuyi opened this issue Feb 6, 2025 · 0 comments

Comments

@duerwuyi
Copy link

duerwuyi commented Feb 6, 2025

How to reproduce

Citus version: Citus 13.0.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
Postgres version: PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) 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 t2;
create table t2 ( 
vkey int4 ,
pkey int4 ,
c10 numeric ,
c11 text ,
c12 int4 ,
c13 int4 ,
c14 text ,
c15 timestamp ,
c16 numeric ,
c17 numeric 
);
DROP TABLE IF EXISTS t4;
create table t4 ( 
vkey int4 ,
pkey int4 ,
c22 numeric ,
c23 text ,
c24 timestamp 
);
DROP TABLE IF EXISTS t6;
create table t6 ( 
vkey int4 ,
pkey int4 ,
c26 text ,
c27 timestamp ,
c28 int4 ,
c29 int4 ,
c30 int4 ,
c31 numeric ,
c32 numeric ,
c33 numeric 
);

SELECT create_reference_table('t2');  
insert into t6 (vkey, pkey, c26, c27, c28, c29, c30, c31, c32, c33) values 
(2, 12000, '', make_timestamp(2037, 2, 3, 13, 55, 31), 69, 0, -0, 82.49, -92.9, 40.7);
insert into t4 (vkey, pkey, c22, c23, c24) values 
(5, 15000, 0.0, ']]?', make_timestamp(2071, 10, 26, 16, 20, 5));
insert into t2 (vkey, pkey, c10, c11, c12, c13, c14, c15, c16, c17) values 
(14, 24000, 28.66, '9rVx', -98, 79, '`', null::timestamp, 56.17, 98.30);
insert into t2 (vkey, pkey, c10, c11, c12, c13, c14, c15, c16, c17) values 
(28, 38000, -0.0, 'f+', -85, null::int4, '', make_timestamp(2033, 3, 8, 1, 20, 19), -65.55, -22.74);

query

update t6 set 
  vkey = 43
where exists (
  select distinct 
      t6.c29,
      (select c15 from t2)
    from
      t4
)

result: 0 rows updated

Excepted result:

the subquery should have rows to match the "EXISTS" clause, so the correct result should be: 1 row updated.

if I changed the query into:

update t6 set 
  vkey = 43
where exists (
  select distinct 
      t6.c29
    from
      t4
)

the answer will be correct.

@m3hm3t m3hm3t self-assigned this Feb 11, 2025
m3hm3t added a commit that referenced this issue Feb 25, 2025
…ith Reference Table in Subquery (#7897)

This PR fixes an issue #7891 in the Citus planner where an `UPDATE` on a
local table with a subquery referencing a reference table could produce
a 0-task plan. Historically, the planner sometimes failed to detect that
both the target and referenced tables were effectively “local,”
assigning `INVALID_SHARD_ID `and yielding a no-op plan.

### Root Cause

- In the Citus router logic (`PlanRouterQuery`), we relied on `shardId`
to determine whether a query should be routed to a single shard.
- If `shardId == INVALID_SHARD_ID`, but we also had not marked the query
as a “local table modification,” the code path would produce zero tasks.
- Local + reference tables do not require multi-shard routing. Failing
to detect this “purely local” scenario caused Citus to incorrectly route
to zero tasks.

### Changes

**Enhanced Local Table Detection**

- Updated `IsLocalTableModification` and related checks to consider both
local and reference tables as “local” for planning, preventing the
0-task scenario.
- Expanded `ContainsOnlyLocalOrReferenceTables` to return true if there
are no fully distributed tables in the query.

**Added Regress Test**

- Introduced a new regress test (`issue_7891.sql`) which reproduces the
scenario.
- Verifies we get a valid single- or local-task plan rather than a
0-task plan.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants